﻿using JYH.Data;
using JYH.DataAccess;
using JYH.Model;
using JYH.Model.API;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace JYH.Business
{
    /// <summary>
    /// 生产领料单
    /// </summary>
    public class WmsProductionPickingOrderBLL : BusinessBase<WmsTradeOrderInfo>
    {
        public WmsProductionPickingOrderBLL()
        {
            SqlSelect = @"";
        }

        /// <summary>
        /// 生产领料单同步
        /// </summary>
        /// <param name="Infos"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public static bool SyncProductionPickingOrder(List<WmsTradeOrderInfo> Infos, out string Msg)
        {
            Msg = string.Empty;
            Database db = DB.D();
            Sql sql = new Sql();
            StringBuilder sb = new StringBuilder();
            db.BeginTransaction();
            try
            {
                string OrderNO = "'',";
                string MaterialNO = "'',";
                string WarehouseNO = "'',";
                string StorageNO = "'',";
                foreach (WmsTradeOrderInfo info in Infos)
                {
                    OrderNO += "'" + info.TradeOrderNO + "',";
                    WarehouseNO += "'" + info.FromWarehouseNO + "',";
                    foreach (WmsTradeSource source in info.TradeSources)
                    {
                        if (MaterialNO.IndexOf(source.MaterialNO) < 0)
                        {
                            MaterialNO += "'" + source.MaterialNO + "',";
                            WarehouseNO += "'" + source.WarehouseNO + "',";
                            WarehouseNO += "'" + source.ToWarehouseNO + "',";
                            StorageNO += "'" + source.StorageNO + "',";
                            StorageNO += "'" + source.ToStorageNO + "',";
                        }
                    }
                }
                OrderNO = OrderNO.Substring(0, OrderNO.Length - 1);
                WarehouseNO = WarehouseNO.Substring(0, WarehouseNO.Length - 1);
                MaterialNO = MaterialNO.Substring(0, MaterialNO.Length - 1);
                StorageNO = StorageNO.Substring(0, StorageNO.Length - 1);
                List<WmsTradeOrderInfo> OldOrders = new List<WmsTradeOrderInfo>();
                sql = new Sql();
                sql.Append(@";Select WmsTradeOrderInfo.*
                                From WmsTradeOrderInfo");
                sql.Where("WmsTradeOrderInfo.TradeOrderNO In (" + OrderNO + ")");
                sql.OrderBy("WmsTradeOrderInfo.TradeOrderNO Desc");
                OldOrders = db.Fetch<WmsTradeOrderInfo>(sql);
                List<WmsTradeSource> OldSouces = new List<WmsTradeSource>();
                sql = new Sql();
                sql.Append(@";Select WmsTradeSource.*,
                                WmsTradeOrderInfo.TradeOrderNO
                                From WmsTradeSource
                                Inner JOin   WmsTradeOrderInfo
                                ON WmsTradeOrderInfo.TradeOrderID=WmsTradeSource.TradeOrderID
                                ");
                sql.Where("WmsTradeOrderInfo.TradeOrderNO In (" + OrderNO + ")");
                sql.OrderBy("WmsTradeOrderInfo.TradeOrderNO Desc,WmsTradeSource.CreateTime");
                OldSouces = db.Fetch<WmsTradeSource>(sql);

                sql = new Sql();
                sql.Append(@";Select BasWarehouseInfo.* From BasWarehouseInfo");
                sql.Where("BasWarehouseInfo.WarehouseNO in (" + WarehouseNO + ")");
                sql.OrderBy("BasWarehouseInfo.WarehouseNO");
                List<BasWarehouseInfo> Warehouses = new List<BasWarehouseInfo>();
                Warehouses = db.Fetch<BasWarehouseInfo>(sql);
                sql = new Sql();
                sql.Append(@";Select BasStorageInfo.* From BasStorageInfo
                             Inner Join BasWarehouseInfo
                             On BasWarehouseInfo.WarehouseID=BasStorageInfo.WarehouseID");
                sql.Where("BasStorageInfo.StorageNO in (" + StorageNO + ")");
                sql.OrderBy("BasWarehouseInfo.WarehouseNO,BasStorageInfo.StorageNO");
                List<BasStorageInfo> Storages = new List<BasStorageInfo>();
                Storages = db.Fetch<BasStorageInfo>(sql);

                sql = new Sql();
                sql.Append(@";Select BasMaterialInfo.* From BasMaterialInfo");
                sql.Where("BasMaterialInfo.MaterialNO in (" + MaterialNO + ")");
                sql.OrderBy("BasMaterialInfo.MaterialNO");
                List<BasMaterialInfo> Materials = new List<BasMaterialInfo>();
                Materials = db.Fetch<BasMaterialInfo>(sql);
                int count = 0;
                for (int i = 0; i < Infos.Count; i++)
                {
                    int index = OldOrders.FindIndex(p => p.TradeOrderNO == Infos[i].TradeOrderNO);
                    if (index < 0)
                    {
                        WmsTradeOrderInfo info = new WmsTradeOrderInfo();
                        info.TradeOrderID = Guid.NewGuid().ToString();
                        info.TradeOrderNO = Infos[i].TradeOrderNO;
                        info.TradeDate = Infos[i].TradeDate;
                        info.RecvType = Infos[i].RecvType;
                        info.TradeType = Infos[i].TradeType;
                        BasWarehouseInfo w = Warehouses.FirstOrDefault(p => p.WarehouseNO == Infos[i].FromWarehouseNO);
                        if (w != null)
                        {
                            info.FromWarehouseID = w.WarehouseID;
                        }
                        info.CreateTime = BusinessTool.GetDateTime();
                        info.CreateUser = Infos[i].CreateUser;
                        info.RState = "0";
                        info.SourceOrderNO = Infos[i].SourceOrderNO;
                        OldOrders.Add(info);
                        count++;
                        sb.AppendLine(BusinessTool.GetInsertSql(info));
                        for (int j = 0; j < Infos[i].TradeSources.Count; j++)
                        {
                            WmsTradeSource source = new WmsTradeSource();
                            source.ActionQty = 0;
                            source.TradeOrderID = info.TradeOrderID;
                            source.TradeSourceID = Guid.NewGuid().ToString();
                            source.SourceOrderNO = Infos[i].TradeSources[j].SourceOrderNO;
                            source.SourceOrderItem = Infos[i].TradeSources[j].SourceOrderItem;
                            source.PONO = Infos[i].TradeSources[j].PONO;
                            source.POItem = Infos[i].TradeSources[j].POItem;
                            source.RequestQty = Infos[i].TradeSources[j].RequestQty;
                            source.ErpItem = Infos[i].TradeSources[j].ErpItem;
                            source.BatchNO = Infos[i].TradeSources[j].BatchNO;
                            source.UnitNO = Infos[i].TradeSources[j].UnitNO;
                            BasMaterialInfo m = Materials.FirstOrDefault(p => p.MaterialNO == Infos[i].TradeSources[j].MaterialNO);
                            if (m != null)
                            {
                                source.MaterialID = m.MaterialID;
                            }
                            BasStorageInfo s = Storages.FirstOrDefault(p => p.StorageNO == Infos[i].TradeSources[j].StorageNO);
                            if (s != null)
                            {
                                source.StorageID = s.StorageID;
                                info.FromWarehouseID = s.WarehouseID;
                            }
                            sb.AppendLine(BusinessTool.GetInsertSql(source));
                            count++;
                        }

                        sb.AppendLine(BusinessTool.GetUpdateSql(info));
                        if (count > 1000)
                        {
                            db.Execute(sb.ToString());
                            sb = new StringBuilder();
                            count = 0;
                        }
                    }
                    else
                    {
                        if (OldOrders[index].RState == "0")
                        {
                            //没有审核
                            sb.Append(@"Delete WmsTradeSource Where TradeOrderID='" + OldOrders[index].TradeOrderID + "'");

                            OldOrders[index].TradeOrderNO = Infos[i].TradeOrderNO;
                            OldOrders[index].TradeDate = Infos[i].TradeDate;
                            OldOrders[index].RecvType = Infos[i].RecvType;

                            BasWarehouseInfo w = Warehouses.FirstOrDefault(p => p.WarehouseNO == Infos[i].FromWarehouseNO);
                            if (w != null)
                            {
                                OldOrders[index].FromWarehouseID = w.WarehouseID;
                            }
                            OldOrders[index].UpdateTime = BusinessTool.GetDateTime();
                            OldOrders[index].UpdateUser = Infos[i].CreateUser;
                            OldOrders[index].RState = "0";
                            OldOrders[index].SourceOrderNO = Infos[i].SourceOrderNO;

                            count++;
                            sb.AppendLine(BusinessTool.GetUpdateSql(OldOrders[index]));
                            for (int j = 0; j < Infos[i].TradeSources.Count; j++)
                            {
                                WmsTradeSource source = new WmsTradeSource();
                                source.ActionQty = 0;
                                source.TradeOrderID = OldOrders[index].TradeOrderID;
                                source.TradeSourceID = Guid.NewGuid().ToString();
                                source.RequestQty = Infos[i].TradeSources[j].RequestQty;
                                source.ErpItem = Infos[i].TradeSources[j].ErpItem;
                                source.BatchNO = Infos[i].TradeSources[j].BatchNO;
                                source.SourceOrderNO = Infos[i].TradeSources[j].SourceOrderNO;
                                source.SourceOrderItem = Infos[i].TradeSources[j].SourceOrderItem;
                                source.PONO = Infos[i].TradeSources[j].PONO;
                                source.POItem = Infos[i].TradeSources[j].POItem;
                                source.RequestQty = Infos[i].TradeSources[j].RequestQty;
                                source.UnitNO = Infos[i].TradeSources[j].UnitNO;
                                BasMaterialInfo m = Materials.FirstOrDefault(p => p.MaterialNO == Infos[i].TradeSources[j].MaterialNO);
                                if (m != null)
                                {
                                    source.MaterialID = m.MaterialID;
                                }
                                BasStorageInfo s = Storages.FirstOrDefault(p => p.StorageNO == Infos[i].TradeSources[j].StorageNO);
                                if (s != null)
                                {
                                    source.StorageID = s.StorageID;
                                    OldOrders[i].FromWarehouseID = s.WarehouseID;
                                }
                                sb.AppendLine(BusinessTool.GetInsertSql(source));
                                count++;
                            }
                            sb.AppendLine(BusinessTool.GetUpdateSql(OldOrders[index]));
                            if (count > 1000)
                            {
                                db.Execute(sb.ToString());
                                sb = new StringBuilder();
                                count = 0;
                            }
                        }
                    }
                }

                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                    sb = new StringBuilder();
                    count = 0;
                }

                db.CompleteTransaction();
                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
                db.AbortTransaction();
                return false;
            }
        }

        /// <summary>
        /// 获取仓库交易对象(含交易主档，需求明细和条码明细)
        /// </summary>
        /// <param name="TradeOrderNO">交易单号和交易ID</param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public static WmsTradeOrderInfo GetProductionPickingOrder(string TradeOrderNO, out string Msg)
        {
            WmsTradeOrderInfo Info = new WmsTradeOrderInfo();
            Msg = string.Empty;

            try
            {
                Info = WmsTradeOrderInfoBLL.GerTradeOrderInfo(TradeOrderNO, out Msg);
                return Info;
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 生产领料单单项次配料
        /// </summary>
        /// <param name="TradeSourceID">需求明细ID</param>
        /// <param name="Msg"></param>
        /// <param name="Barcodes">交易条码列表</param>
        /// <returns></returns>
        public static bool TradeOrderPickBarcode(string TradeSourceID, string UserNO, out string Msg, out List<WmsTradebarcode> Barcodes)
        {
            Msg = string.Empty;
            Sql sql = new Sql();
            StringBuilder sb = new StringBuilder();
            Barcodes = new List<WmsTradebarcode>();
            Database db = DB.D();
            db.BeginTransaction();
            try
            {
                sql = new Sql();
                sql.Append(@"Select WmsTradeSource.* From WmsTradeSource");
                sql.Where("WmsTradeSource.TradeSourceID='" + TradeSourceID + "'");
                WmsTradeSource source = db.FirstOrDefault<WmsTradeSource>(sql);
                if (source == null)
                {
                    Msg = "NO Detail";
                    db.AbortTransaction();
                    return false;
                }
                sql = new Sql();
                sql.Append(@"Select WmsStockBarcode.*,
                                    BasMaterialInfo.MaterialNO,
                                    BasMaterialInfo.MaterialName,
                                    BasBarcodeInfo.BatchNO,
                                    BasBarcodeInfo.BarcodeNO,
                                    BasStorageInfo.StorageNO,
                                    BasWarehouseInfo.WarehouseNO,
                                    BasLampInfo.LampNO
                                    From WmsStockBarcode
                                    Inner JOin BasBarcodeInfo On WmsStockBarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                                    Inner JOIn WmsTradeSource  On WmsStockBarcode.MaterialID=WmsTradeSource.MaterialID
                                    And WmsStockBarcode.StorageID=WmsTradeSource.StorageID
                                    And BasBarcodeInfo.BatchNO=WmsTradeSource.BatchNO
                                    ANd WmsTradeSource.TradeSourceID='" + TradeSourceID + @"'
                                    Inner JOin BasStorageInfo  ON BasStorageInfo.StorageID=WmsStockBarcode.StorageID
                                    Inner Join  BasWarehouseInfo ON BasStorageInfo.WarehouseID=BasWarehouseInfo.WarehouseID
                                    Inner JOin BasMaterialInfo On BasMaterialInfo.MaterialID=WmsStockBarcode.MaterialID
                                    Left JOin BasLampInfo On WmsStockBarcode.LampID=BasLampInfo.LampID");
                sql.Where(@" WmsStockBarcode.IsLock=0");
                sql.OrderBy(" WmsStockBarcode.StockQty");
                List<WmsStockBarcode> Stocks = db.Fetch<WmsStockBarcode>(sql);
                if (Stocks.Count < 1)
                {
                    Msg = "No Barcode ";
                }
                else
                {
                    for (int i = 0; i < Stocks.Count; i++)
                    {
                        if (source.RequestQty <= source.CalculateQty)
                        {
                            break;
                        }
                        else
                        {
                            Stocks[i].IsLock = 1;
                            sb.Append(BusinessTool.GetUpdateSql(Stocks[i]));
                            source.CalculateQty += Stocks[i].StockQty;
                            sb.Append(BusinessTool.GetUpdateSql(source));
                            WmsTradebarcode tradebarcode = new WmsTradebarcode();
                            tradebarcode.BarcodeID = Stocks[i].BarcodeID;
                            tradebarcode.TradeSourceID = source.TradeSourceID;
                            tradebarcode.TradeOrderID = source.TradeOrderID;
                            tradebarcode.WarehouseID = Stocks[i].WarehouseID;
                            tradebarcode.StorageID = Stocks[i].StorageID;
                            tradebarcode.LampID = Stocks[i].LampID;
                            tradebarcode.Qty = Stocks[i].StockQty;
                            tradebarcode.RState = "0";
                            tradebarcode.MaterialID = Stocks[i].MaterialID;
                            tradebarcode.TradebarcodeID = Guid.NewGuid().ToString();
                            tradebarcode.BatchNO = source.BatchNO;
                            tradebarcode.CreateTime = BusinessTool.GetDateTime();
                            tradebarcode.CreateUser = UserNO;
                            sb.Append(BusinessTool.GetInsertSql(tradebarcode));
                        }
                    }
                    if (sb.Length > 0)
                    {
                        db.Execute(sb.ToString());
                    }
                }
       
   
                sql = new Sql();
                sql.Append(@";Select
                            WmsTradebarcode.*,
                            BasBarcodeInfo.BarcodeNO,
                            BasBarcodeInfo.BatchNO,
                            CONVERT(INT, isnull( WmsTradeSource.ErpItem,0)) ErpItem,
                            WmsTradeOrderInfo.TradeOrderNO,
                            BasMaterialInfo.MaterialNO,
                            BasStorageInfo.StorageNO StorageNO,
                            BasWarehouseInfo.WarehouseNO FromWarehouseNO,
                            BasLampInfo.LampNO LampNO,
                            BasLampInfo.LampCode,
                            ToStorageInfo.StorageNO ToStorageNO,
                            ToWarehouseInfo.WarehouseNO ToWarehouseNO,
                            ToLampInfo.LampNO ToLampNO
                            From WmsTradebarcode
                            Inner JOin WmsTradeOrderInfo ON WmsTradeOrderInfo.TradeOrderID=WmsTradebarcode.TradeOrderID
                            Inner JOIn BasBarcodeInfo ON WmsTradebarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                            Inner JOin WmsTradeSource ON WmsTradebarcode.TradeSourceID=WmsTradeSource.TradeSourceID
                            Inner Join BasMaterialInfo ON BasBarcodeInfo.MaterialID=BasMaterialInfo.MaterialID
                            Inner JOIn BasStorageInfo ON WmsTradebarcode.StorageID=BasStorageInfo.StorageID
                            Inner JOIn BasWarehouseInfo ON BasStorageInfo.WarehouseID=BasWarehouseInfo.WarehouseID
                            Left JOIn  BasLampInfo ON WmsTradebarcode.LampID=BasLampInfo.LampID
                            Left JOIn BasStorageInfo ToStorageInfo ON WmsTradebarcode.ToStorageID=ToStorageInfo.StorageID
                            Left JOIn BasWarehouseInfo ToWarehouseInfo ON ToStorageInfo.WarehouseID=ToWarehouseInfo.WarehouseID
                            Left JOIn  BasLampInfo ToLampInfo ON WmsTradebarcode.ToLampID=ToLampInfo.LampID");
                sql.Where("(WmsTradebarcode.TradeSourceID='" + source.TradeSourceID + "')");
                sql.OrderBy("WmsTradeOrderInfo.TradeOrderNO,CONVERT(INT, isnull( WmsTradeSource.ErpItem,0)),WmsTradebarcode.RState,BasBarcodeInfo.BarcodeNO");
               Barcodes = db.Fetch<WmsTradebarcode>(sql);

  


                db.CompleteTransaction();
                return true;
            }
            catch (Exception ex)
            { 
                Msg=ex.ToString();
                db.AbortTransaction();
                return false;
            }
        }



        /// <summary>
        /// 生产领料单单项次配料
        /// </summary>
        /// <param name="TradeSourceID">需求明细ID</param>
        /// <param name="Msg"></param>
        /// <param name="Barcodes">交易条码列表</param>
        /// <param name="db">数据库对象</param>
        /// <returns></returns>
        public static bool TradeOrderPickBarcode(string TradeSourceID, string UserNO, out string Msg, out List<WmsTradebarcode> Barcodes,ref Database db)
        {
            Msg = string.Empty;
            Sql sql = new Sql();
            StringBuilder sb = new StringBuilder();
            Barcodes = new List<WmsTradebarcode>();

            try
            {
                sql = new Sql();
                sql.Append(@"Select WmsTradeSource.* From WmsTradeSource");
                sql.Where("WmsTradeSource.TradeSourceID='" + TradeSourceID + "'");
                WmsTradeSource source = db.FirstOrDefault<WmsTradeSource>(sql);
                if (source == null)
                {
                    Msg = "NO Detail";
                
                    return false;
                }
                sql = new Sql();
                sql.Append(@"Select WmsStockBarcode.*,
                                    BasMaterialInfo.MaterialNO,
                                    BasMaterialInfo.MaterialName,
                                    BasBarcodeInfo.BatchNO,
                                    BasBarcodeInfo.BarcodeNO,
                                    BasStorageInfo.StorageNO,
                                    BasWarehouseInfo.WarehouseNO,
                                    BasLampInfo.LampNO
                                    From WmsStockBarcode
                                    Inner JOin BasBarcodeInfo On WmsStockBarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                                    Inner JOIn WmsTradeSource  On WmsStockBarcode.MaterialID=WmsTradeSource.MaterialID
                                    And WmsStockBarcode.StorageID=WmsTradeSource.StorageID
                                    And BasBarcodeInfo.BatchNO=WmsTradeSource.BatchNO
                                    ANd WmsTradeSource.TradeSourceID='" + TradeSourceID + @"'
                                    Inner JOin BasStorageInfo  ON BasStorageInfo.StorageID=WmsStockBarcode.StorageID
                                    Inner Join  BasWarehouseInfo ON BasStorageInfo.WarehouseID=BasWarehouseInfo.WarehouseID
                                    Inner JOin BasMaterialInfo On BasMaterialInfo.MaterialID=WmsStockBarcode.MaterialID
                                    Left JOin BasLampInfo On WmsStockBarcode.LampID=BasLampInfo.LampID");
                sql.Where(@" WmsStockBarcode.IsLock=0");
                sql.OrderBy(" WmsStockBarcode.StockQty");
                List<WmsStockBarcode> Stocks = db.Fetch<WmsStockBarcode>(sql);
                if (Stocks.Count < 1)
                {
                    Msg = "No Barcode ";
                }
                else
                {
                    for (int i = 0; i < Stocks.Count; i++)
                    {
                        if (source.RequestQty <= source.CalculateQty)
                        {
                            break;
                        }
                        else
                        {
                            Stocks[i].IsLock = 1;
                            sb.Append(BusinessTool.GetUpdateSql(Stocks[i]));
                            source.CalculateQty += Stocks[i].StockQty;
                            sb.Append(BusinessTool.GetUpdateSql(source));
                            WmsTradebarcode tradebarcode = new WmsTradebarcode();
                            tradebarcode.BarcodeID = Stocks[i].BarcodeID;
                            tradebarcode.TradeSourceID = source.TradeSourceID;
                            tradebarcode.TradeOrderID = source.TradeOrderID;
                            tradebarcode.WarehouseID = Stocks[i].WarehouseID;
                            tradebarcode.StorageID = Stocks[i].StorageID;
                            tradebarcode.LampID = Stocks[i].LampID;
                            tradebarcode.Qty = Stocks[i].StockQty;
                            tradebarcode.RState = "0";
                            tradebarcode.MaterialID = Stocks[i].MaterialID;
                            tradebarcode.TradebarcodeID = Guid.NewGuid().ToString();
                            tradebarcode.BatchNO = source.BatchNO;
                            tradebarcode.CreateTime = BusinessTool.GetDateTime();
                            tradebarcode.CreateUser = UserNO;
                            sb.Append(BusinessTool.GetInsertSql(tradebarcode));
                        }
                    }
                    if (sb.Length > 0)
                    {
                        db.Execute(sb.ToString());
                    }
                }


               



                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
      
                return false;
            }
        }
        /// <summary>
        /// 整单捡料(只是查询不算料)
        /// </summary>
        /// <param name="TradeOrderNO">出库单号</param>
        /// <param name="Msg"></param>
        /// <param name="Barocdes">记录内容（分页）</param>
        /// <param name="Total">总记录数</param>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页记录数</param>
        /// <returns></returns>
        public static bool TradeOrderPickBarcode(string TradeOrderNO, out string Msg, out List<WmsTradebarcode> Barocdes, out long Total, long PageIndex = 1, long PageSize = 20)
        {
            Msg=String.Empty;
            Barocdes = new List<WmsTradebarcode>();
            Database db = DB.D();

            Sql sql = new Sql();
            Total = 0;
            try
            {
               

                sql = new Sql();
                sql.Append(@";Select
                            WmsTradebarcode.*,
                            BasBarcodeInfo.BarcodeNO,
                            BasBarcodeInfo.BatchNO,
                            CONVERT(INT, isnull( WmsTradeSource.ErpItem,0)) ErpItem,
                            WmsTradeOrderInfo.TradeOrderNO,
                            BasMaterialInfo.MaterialNO,
                            BasStorageInfo.StorageNO StorageNO,
                            BasWarehouseInfo.WarehouseNO FromWarehouseNO,
                            BasLampInfo.LampNO LampNO,
                            BasLampInfo.LampCode,
                            ToStorageInfo.StorageNO ToStorageNO,
                            ToWarehouseInfo.WarehouseNO ToWarehouseNO,
                            ToLampInfo.LampNO ToLampNO
                            From WmsTradebarcode 
                            Inner JOin WmsTradeOrderInfo ON WmsTradeOrderInfo.TradeOrderID=WmsTradebarcode.TradeOrderID
                            Inner JOIn BasBarcodeInfo ON WmsTradebarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                            Inner JOin WmsTradeSource ON WmsTradebarcode.TradeSourceID=WmsTradeSource.TradeSourceID
                            Inner Join BasMaterialInfo ON BasBarcodeInfo.MaterialID=BasMaterialInfo.MaterialID
                            Inner JOIn BasStorageInfo ON WmsTradebarcode.StorageID=BasStorageInfo.StorageID
                            Inner JOIn BasWarehouseInfo ON BasStorageInfo.WarehouseID=BasWarehouseInfo.WarehouseID
                            Left JOIn  BasLampInfo ON WmsTradebarcode.LampID=BasLampInfo.LampID
                            Left JOIn BasStorageInfo ToStorageInfo ON WmsTradebarcode.ToStorageID=ToStorageInfo.StorageID
                            Left JOIn BasWarehouseInfo ToWarehouseInfo ON ToStorageInfo.WarehouseID=ToWarehouseInfo.WarehouseID
                            Left JOIn  BasLampInfo ToLampInfo ON WmsTradebarcode.ToLampID=ToLampInfo.LampID");
                sql.Where("(WmsTradeOrderInfo.TradeOrderNO='" + TradeOrderNO + "')");
                sql.OrderBy("WmsTradeOrderInfo.TradeOrderNO,WmsTradebarcode.RState,CONVERT(INT, isnull( WmsTradeSource.ErpItem,0)),BasBarcodeInfo.BarcodeNO");
                Page<WmsTradebarcode> page = db.Page<WmsTradebarcode>(PageIndex, PageSize, sql);

                Barocdes = page.Items;
                Total = page.TotalItems;


                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
          
                return false;
            }
        }


        /// <summary>
        /// 整单捡料(算料+分页查询)
        /// </summary>
        /// <param name="TradeOrderNO">交易单号</param>
        /// <param name="UserNO">操作用户</param>
        /// <param name="Msg">反馈信息</param>
        /// <param name="Barocdes">分页条码集合</param>
        /// <param name="Total">总记录数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <returns></returns>
        public static bool TradeOrderPickBarcode(string TradeOrderNO, string UserNO, out string Msg, out List<WmsTradebarcode> Barocdes, out long Total, long PageIndex = 1, long PageSize = 20)
        {
            Msg = String.Empty;
            Barocdes = new List<WmsTradebarcode>();
            Database db = DB.D();
            db.BeginTransaction();
            Sql sql = new Sql();
            Total = 0;
            try
            {
                sql = new Sql();
                sql.Append(@"Select WmsTRadeSource.* From WmsTRadeSource
                             Inner JOin WmsTradeOrderInfo ON WmsTradeOrderInfo.TradeOrderID=WmsTRadeSource.TradeOrderID");
                sql.Where("WmsTradeOrderInfo.TradeOrderNO='" + TradeOrderNO + "'");
                sql.OrderBy("WmsTradeOrderInfo.TradeOrderNO,CONVERT(INT, isnull( WmsTradeSource.ErpItem,0))");
                List<WmsTradeSource> tradeSources=db.Fetch<WmsTradeSource>(sql);
                if (tradeSources == null || tradeSources.Count == 0)
                {
                    Msg = "NO Detail";
                    db.AbortTransaction();
                    return false;

                }
                foreach (WmsTradeSource source in tradeSources)
                {
                    bool res = TradeOrderPickBarcode(source.TradeSourceID, UserNO, out Msg, out Barocdes,ref db);
                    if (!res)
                    {
                        db.AbortTransaction();
                        return false;
                    }
                }

                db.CompleteTransaction();
                sql = new Sql();
                sql.Append(@";Select
                            WmsTradebarcode.*,
                            BasBarcodeInfo.BarcodeNO,
                            BasBarcodeInfo.BatchNO,
                            CONVERT(INT, isnull( WmsTradeSource.ErpItem,0)) ErpItem,
                            WmsTradeOrderInfo.TradeOrderNO,
                            BasMaterialInfo.MaterialNO,
                            BasStorageInfo.StorageNO StorageNO,
                            BasWarehouseInfo.WarehouseNO FromWarehouseNO,
                            BasLampInfo.LampNO LampNO,
                            BasLampInfo.LampCode,
                            ToStorageInfo.StorageNO ToStorageNO,
                            ToWarehouseInfo.WarehouseNO ToWarehouseNO,
                            ToLampInfo.LampNO ToLampNO
                            From WmsTradebarcode 
                            Inner JOin WmsTradeOrderInfo ON WmsTradeOrderInfo.TradeOrderID=WmsTradebarcode.TradeOrderID
                            Inner JOIn BasBarcodeInfo ON WmsTradebarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                            Inner JOin WmsTradeSource ON WmsTradebarcode.TradeSourceID=WmsTradeSource.TradeSourceID
                            Inner Join BasMaterialInfo ON BasBarcodeInfo.MaterialID=BasMaterialInfo.MaterialID
                            Inner JOIn BasStorageInfo ON WmsTradebarcode.StorageID=BasStorageInfo.StorageID
                            Inner JOIn BasWarehouseInfo ON BasStorageInfo.WarehouseID=BasWarehouseInfo.WarehouseID
                            Left JOIn  BasLampInfo ON WmsTradebarcode.LampID=BasLampInfo.LampID
                            Left JOIn BasStorageInfo ToStorageInfo ON WmsTradebarcode.ToStorageID=ToStorageInfo.StorageID
                            Left JOIn BasWarehouseInfo ToWarehouseInfo ON ToStorageInfo.WarehouseID=ToWarehouseInfo.WarehouseID
                            Left JOIn  BasLampInfo ToLampInfo ON WmsTradebarcode.ToLampID=ToLampInfo.LampID");
                sql.Where("(WmsTradeOrderInfo.TradeOrderNO='" + TradeOrderNO + "')");
                sql.OrderBy("WmsTradeOrderInfo.TradeOrderNO,WmsTradebarcode.RState,CONVERT(INT, isnull( WmsTradeSource.ErpItem,0)),BasBarcodeInfo.BarcodeNO");
                Page<WmsTradebarcode> page = db.Page<WmsTradebarcode>(PageIndex, PageSize, sql);

                Barocdes = page.Items;
                Total = page.TotalItems;


                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                db.AbortTransaction();
                return false;
            }
        }
        /// <summary>
        /// 单据发料下架（批量）
        /// </summary>
        /// <param name="lists"></param>
        /// <param name="ligthCheck"></param>
        /// <param name="orderNO"></param>
        /// <param name="json"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public static bool DBMaterialBarcode(ref List<WmsStockBarcode> info, string OrderNO, string TradeSourceID, string UserNO, ref ResponseJson<WmsStockBarcode> responseJson)
        {
            Sql sql = new Sql();
            StringBuilder sb = new StringBuilder();
            Database db = DB.D();
            db.BeginTransaction();
            bool falg = false;
            try
            {
                sql = new Sql();
                sql.Append(@";Select WmsTradebarcode.*,BasLampInfo.LampNO,BasLampInfo.LampCode
                            From WmsTradebarcode
                            Inner JOin WmsTradeOrderInfo ON WmsTradebarcode.TradeOrderID=WmsTradeOrderInfo.TradeOrderID
                            Inner JOIn BasBarcodeInfo ON WmsTradebarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                            Left JOIn BasLampInfo ON WmsTradebarcode.LampID=BasLampInfo.LampID");
                sql.Where("WmsTradeOrderInfo.TradeOrderNO='" + OrderNO + "'");
                if (!string.IsNullOrEmpty(TradeSourceID))
                {
                    sql.Where("WmsTradebarcode.TradeSourceID='" + TradeSourceID + "'");
                }
                string NOs = "'',";
                foreach (WmsStockBarcode barcode in info)
                {
                    NOs += "'" + barcode.BarcodeNO + "',";
                }
                NOs = NOs.Substring(0, NOs.Length - 1);
                sql.Where("BasBarcodeInfo.BarcodeNO in (" + NOs + ")");
                List<WmsTradebarcode> tradebarcodes = db.Fetch<WmsTradebarcode>(sql);
                if (tradebarcodes.Count < info.Count)
                {
                    responseJson.Message = "Barcode Is In Stock";
                    return false;
                }
                List<string> LampCodes = new List<string>();
                foreach (WmsTradebarcode barcode in tradebarcodes)
                {
                    if (barcode.RState == "0")
                    {
                        barcode.RState = "1";
                        barcode.UpdateUser = UserNO;
                        barcode.UpdateTime = BusinessTool.GetDateTime();
                        sb.Append(@"UPdate WmsTradeSource set ActionQty=ActionQty+" + barcode.Qty + " where TradeSourceID='" + barcode.TradeSourceID + "'");
                        sb.Append(@"Update BasLampInfo set RState='1' where LampID='" + barcode.LampID + "'");
                        sb.Append(BusinessTool.GetUpdateSql(barcode));
                        if (!string.IsNullOrEmpty(barcode.LampCode))
                            LampCodes.Add(barcode.LampCode);
                    }
                }

                if (LampCodes.Count > 0)
                {
                    if (!NetWorkLayerHelper.OnLampCode((int)ColorCode.Green, LampCodes, true, false))
                    {
                        db.AbortTransaction();
                        responseJson.Message = ErrorCode.Admin.GetMessage();
                        return false;
                    }
                    foreach (var lampCode in LampCodes)
                    {
                        BasLampControl ctr = new BasLampControl();
                        ctr.LampControlID = Guid.NewGuid().ToString();
                        ctr.RState = "0";
                        ctr.NColor = 0;
                        ctr.LampNO = lampCode;
                        ctr.DoTime = DateTime.Now.AddMinutes(1).ToString("yyyy-MM-dd HH:mm:ss");
                        sb.Append(BusinessTool.GetInsertSql(ctr));
                    }
                }
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }

                db.CompleteTransaction();
                return true;
            }
            catch (Exception ex)
            {
                db.AbortTransaction();
                responseJson.Message = ex.ToString();
                return false;
            }
        }



        /// <summary>
        /// 单据发料下架（批量）
        /// </summary>
        /// <param name="info"></param>
        /// <param name="OrderNO"></param>
        /// <param name="UserNO"></param>
        /// <param name="responseJson"></param>
        /// <returns></returns>
        public static bool DBMaterialBarcode(ref List<WmsStockBarcode> info, string OrderNO,string UserNO, ref ResponseJson<WmsStockBarcode> responseJson)
        {
            Sql sql = new Sql();
            StringBuilder sb = new StringBuilder();
            Database db = DB.D();
            db.BeginTransaction();
            bool falg = false;
            try
            {
                sql = new Sql();
                sql.Append(@";Select WmsTradebarcode.*,BasLampInfo.LampNO,BasLampInfo.LampCode
                            From WmsTradebarcode
                            Inner JOin WmsTradeOrderInfo ON WmsTradebarcode.TradeOrderID=WmsTradeOrderInfo.TradeOrderID
                            Inner JOIn BasBarcodeInfo ON WmsTradebarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                            Left JOIn BasLampInfo ON WmsTradebarcode.LampID=BasLampInfo.LampID");
                sql.Where("WmsTradeOrderInfo.TradeOrderNO='" + OrderNO + "'");
               
                string NOs = "'',";
                foreach (WmsStockBarcode barcode in info)
                {
                    NOs += "'" + barcode.BarcodeNO + "',";
                }
                NOs = NOs.Substring(0, NOs.Length - 1);
                sql.Where("BasBarcodeInfo.BarcodeNO in (" + NOs + ")");
                List<WmsTradebarcode> tradebarcodes = db.Fetch<WmsTradebarcode>(sql);
                if (tradebarcodes.Count < info.Count)
                {
                    responseJson.Message = "Barcode Is In Stock";
                    return false;
                }
                List<string> LampCodes = new List<string>();
                foreach (WmsTradebarcode barcode in tradebarcodes)
                {
                    if (barcode.RState == "0")
                    {
                        barcode.RState = "1";
                        barcode.UpdateUser = UserNO;
                        barcode.UpdateTime = BusinessTool.GetDateTime();
                        sb.Append(@"UPdate WmsTradeSource set ActionQty=ActionQty+" + barcode.Qty + " where TradeSourceID='" + barcode.TradeSourceID + "'");
                        sb.Append(@"Update BasLampInfo set RState='1' where LampID='" + barcode.LampID + "'");
                        sb.Append(BusinessTool.GetUpdateSql(barcode));
                        if (!string.IsNullOrEmpty(barcode.LampCode))
                            LampCodes.Add(barcode.LampCode);
                    }
                }

                if (LampCodes.Count > 0)
                {
                    if (!NetWorkLayerHelper.OnLampCode((int)ColorCode.Green, LampCodes, true, false))
                    {
                        db.AbortTransaction();
                        responseJson.Message = ErrorCode.Admin.GetMessage();
                        return false;
                    }
                    foreach (var lampCode in LampCodes)
                    {
                        BasLampControl ctr = new BasLampControl();
                        ctr.LampControlID = Guid.NewGuid().ToString();
                        ctr.RState = "0";
                        ctr.NColor = 0;
                        ctr.LampNO = lampCode;
                        ctr.DoTime = DateTime.Now.AddMinutes(1).ToString("yyyy-MM-dd HH:mm:ss");
                        sb.Append(BusinessTool.GetInsertSql(ctr));
                    }
                }
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }

                db.CompleteTransaction();
                return true;
            }
            catch (Exception ex)
            {
                db.AbortTransaction();
                responseJson.Message = ex.ToString();
                return false;
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="Infos"></param>
        /// <param name="UserNO"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public static bool UpCheckWmsTradeOrderInfo(List<WmsTradeOrderInfo> Infos, string UserNO, out string Msg)
        {
            Msg = string.Empty;
            Database db = DB.D();
            StringBuilder sb = new StringBuilder();
            db.BeginTransaction();
            try
            {
                foreach (WmsTradeOrderInfo info in Infos)
                {
                    //更新单据状态
                    sb.Append(@"Update WmsTradeOrderInfo Set RState='0',UpdateTime='" + BusinessTool.GetDateTime() + "',UpdateUser='" + UserNO + "' Where TradeOrderID='" + info.TradeOrderID + "' Or  TradeOrderNO='" + info.TradeOrderNO + "'");
                    //初始需求明细
                    sb.Append(@"Update WmsTradeSource
                                    Set ActionQty=0,CalculateQty=0
                                    From WmsTradeSource
                                    Inner JOin WmsTradeOrderInfo ON WmsTradeSource.TradeOrderID=WmsTradeOrderInfo.TradeOrderID
                                    And( WmsTradeOrderInfo.TradeOrderID='" + info.TradeOrderID + "' or  WmsTradeOrderInfo.TradeOrderNO='" + info.TradeOrderNO + "')");
                    //解锁在库条码
                    sb.Append(@"Update WmsStockBarcode
                                Set IsLock=0
                                From WmsStockBarcode
                                Inner JOin WmsTradebarcode On WmsStockBarcode.BarcodeID=WmsTradebarcode.BarcodeID
                                Inner JOin WmsTradeOrderInfo ON WmsTradebarcode.TradeOrderID=WmsTradeOrderInfo.TradeOrderID
                                And( WmsTradeOrderInfo.TradeOrderID='" + info.TradeOrderID + "' or  WmsTradeOrderInfo.TradeOrderNO='" + info.TradeOrderNO + "')");
                    //删除捡料条码
                    sb.Append(@"delete  WmsTradebarcode
                                From WmsTradebarcode
                                Inner JOin WmsTradeOrderInfo ON WmsTradebarcode.TradeOrderID=WmsTradeOrderInfo.TradeOrderID
                                And( WmsTradeOrderInfo.TradeOrderID='" + info.TradeOrderID + "' or  WmsTradeOrderInfo.TradeOrderNO='" + info.TradeOrderNO + "')");
                }

                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }
                db.CompleteTransaction();
                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                db.AbortTransaction();
                return false;
            }
        }

        /// <summary>
        /// 生产领料单登账
        /// </summary>
        /// <param name="Info"> 生产领料单对象</param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public static bool AccountWmsTradeOrderInfo(WmsTradeOrderInfo Info, out string Msg)
        {
            Msg = string.Empty;
            Database db = DB.D();
            db.BeginTransaction();
            StringBuilder sb = new StringBuilder();
            Sql sql = new Sql();
            try
            {
                sql = new Sql();
                sql.Append(@"Select WmsTradeOrderInfo.* From WmsTradeOrderInfo");
                sql.Where("WmsTradeOrderInfo.TradeOrderID='" + Info.TradeOrderID + "' or WmsTradeOrderInfo.TradeOrderNO='" + Info.TradeOrderNO + "'");
                WmsTradeOrderInfo Obj = db.FirstOrDefault<WmsTradeOrderInfo>(sql);
                if (Obj == null)
                {
                    Msg = "NO Order";
                    db.AbortTransaction();
                    return false;
                }
                if (Obj.TradeType != 3)
                {
                    Msg = " Order Type Is Error";
                    db.AbortTransaction();
                    return false;
                }
                if (Obj.RState != "1")
                {
                    Msg = " Order State Is Not Checked";
                    db.AbortTransaction();
                    return false;
                }
                Obj.RState = "2";
                Obj.ApproveDate = Info.TradeDate;
                Obj.ApproveUser = Info.ApproveUser;
                sb.Append(BusinessTool.GetUpdateSql(Obj));
                List<WmsTradebarcode> tradebarocdes = new List<WmsTradebarcode>();
                sql = new Sql();
                sql.Append(@"Select WmsTradebarcode.*,BasBarcodeInfo.BatchNO,BasBarcodeInfo.MaterialID From WmsTradebarcode
                           Inner JOin BasBarcodeInfo On WmsTradebarcode.BarcodeID=BasBarcodeInfo.BarcodeID ");
                sql.Where("WmsTradebarcode.TradeOrderID='" + Obj.TradeOrderID + "'");
                sql.OrderBy("WmsTradebarcode.Rstate");
                tradebarocdes = db.Fetch<WmsTradebarcode>(sql);
                if (tradebarocdes != null && tradebarocdes.Count > 0)
                {
                    if (tradebarocdes[0].RState == "0")
                    {
                        Msg = " Have Some Barcode Not Put Off";
                        db.AbortTransaction();
                        return false;
                    }

                    List<WmsStockBarcode> stockBarcodes = new List<WmsStockBarcode>();
                    List<BasBarcodeInfo> barodes = new List<BasBarcodeInfo>();
                    List<WmsStockMaterial> stockMaterials = new List<WmsStockMaterial>();
                    sql = new Sql();
                    sql.Append(@"select WmsStockBarcode.*,BasBarcodeInfo.BatchNO from WmsStockBarcode
                                Inner JOin BasBarcodeInfo On BasBarcodeInfo.BarcodeID=WmsStockBarcode.BarcodeID
                                Inner JOin WmsTradebarcode On WmsStockBarcode.BarcodeID=WmsTradebarcode.BarcodeID
                                And WmsTradebarcode.TradeOrderID='" + Obj.TradeOrderID + "'");
                    barodes = db.Fetch<BasBarcodeInfo>(sql);
                    stockBarcodes = db.Fetch<WmsStockBarcode>(sql);
                    if (stockBarcodes == null || stockBarcodes.Count != tradebarocdes.Count || barodes.Count != tradebarocdes.Count)
                    {
                        Msg = " Have Some Barcode Not In Stock";
                        db.AbortTransaction();
                        return false;
                    }
                    sql = new Sql();
                    sql.Append(@"select distinct WmsStockMaterial.*,BasMaterialInfo.MaterialNO,BasStorageInfo.StorageNO from WmsStockMaterial
                                    Inner JOin WmsTradeSource On WmsStockMaterial.MaterialID=WmsTradeSource.MaterialID
                                    And WmsTradeSource.BatchNO=WmsStockMaterial.BatchNO
                                    And WmsTradeSource.TradeOrderID='" + Obj.TradeOrderID + @"'
                                    Inner JOIn BasMaterialInfo ON BasMaterialInfo.MaterialID=WmsStockMaterial.MaterialID
                                    Inner JOIn BasStorageInfo ON WmsStockMaterial.StorageID=BasStorageInfo.StorageID");
                    stockMaterials = db.Fetch<WmsStockMaterial>(sql);
                    foreach (WmsTradebarcode barcode in tradebarocdes)
                    {
                        int index = -1;
                        index = stockBarcodes.FindIndex(p => p.BarcodeID == barcode.BarcodeID);

                        if (index < 0)
                        {
                            Msg = " Have Some Barcode Not In Stock";
                            db.AbortTransaction();
                            return false;
                        }
                        else
                        {
                            sb.Append(BusinessTool.GetDeleteSql(stockBarcodes[index]));
                        }
                        index = -1;
                        index = stockMaterials.FindIndex(p => p.MaterialID == barcode.MaterialID && p.BatchNO == barcode.BatchNO && p.StorageID == barcode.StorageID);
                        if (index < 0)
                        {
                            Msg = " Have Some Barcode Not In Stock";
                            db.AbortTransaction();
                            return false;
                        }
                        else
                        {
                            if (stockMaterials[index].StockQty < barcode.Qty)
                            {
                                Msg = " Out Of Stock ";
                                db.AbortTransaction();
                                return false;
                            }
                            else
                            {
                                stockMaterials[index].StockQty -= barcode.Qty;
                                sb.Append(BusinessTool.GetUpdateSql(stockMaterials[index]));
                            }
                        }
                        barcode.RState = "3";
                        sb.Append(BusinessTool.GetUpdateSql(barcode));
                    }
                }

                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }

                db.CompleteTransaction();
                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                db.AbortTransaction();
                return false;
            }
        }
    }
}